package IT.DataAccess;

import IT.Entities.Ubigeo;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import java.util.logging.Level;
import java.util.logging.Logger;

public class DatUbigeo implements IDatUbigeo{
    
    private Ubigeo oUbigeo;
    private List<Ubigeo> oLista;
    private BDConexion oBase;
    
    private Connection oConexion;
    private CallableStatement oCst;    
    private ResultSet oRst;    
    
    public List<Ubigeo> Listar() {
        oLista=new ArrayList<Ubigeo>();
         
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_UbigeoListar()}");
            oRst = oCst.executeQuery();

            while (oRst.next()){
                oLista.add(new Ubigeo(oRst.getString("cod_ubi_ch"), oRst.getString("des_ubi_vc")));
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oLista;
    }

    public Ubigeo Ind(String cCodigo) {
        oUbigeo=new Ubigeo();
         
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_UbigeoInd(?)}");
            oCst.setString(1, cCodigo);
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oUbigeo.setCod_ubi_ch(oRst.getString("cod_ubi_ch"));
                oUbigeo.setDes_ubi_vc(oRst.getString("des_ubi_vc"));
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oUbigeo;
    }

    public List<Ubigeo> ListarDepartamento() {
        oLista=new ArrayList<Ubigeo>();
         
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_UbigeoDepartamentos()}");
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oLista.add(new Ubigeo(oRst.getString("cod_ubi_ch"), oRst.getString("des_ubi_vc")));
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oLista;
    }

    public List<Ubigeo> ListarProvincia(String cDeparmento) {
        oLista=new ArrayList<Ubigeo>();
         
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_UbigeoProvincia(?)}");
            oCst.setString(1, cDeparmento);
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oLista.add(new Ubigeo(oRst.getString("cod_ubi_ch"), oRst.getString("des_ubi_vc")));
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oLista;
    }

    public List<Ubigeo> ListarDistrito(String cProvincia) {
         oLista=new ArrayList<Ubigeo>();
         
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_UbigeoDistrito(?)}");
            oCst.setString(1, cProvincia);
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oLista.add(new Ubigeo(oRst.getString("cod_ubi_ch"), oRst.getString("des_ubi_vc")));
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
        return oLista;
    }

    public void Insertar(Ubigeo ent) {
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_UbigeoInsertar(?,?)}");  
            oCst.setString(1, ent.getCod_ubi_ch());
            oCst.setString(2, ent.getDes_ubi_vc());
            
            oCst.execute();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void Editar(Ubigeo ent) {
         try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_UbigeoEditar(?,?)}");  
            oCst.setString(1, ent.getCod_ubi_ch());
            oCst.setString(2, ent.getDes_ubi_vc());
            
            oCst.execute();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void Eliminar(String cCodigo) {
         try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_UbigeoEliminar(?)}");  
            oCst.setString(1, cCodigo);        
            
            oCst.execute();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

}
